package cn.rengy.isql;

import cn.rengy.isql.lang._StopWatch;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.util.FileCopyUtils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

@Slf4j
//@ConditionalOnBean(DataSourceAutoConfiguration.class)
public class Dao {


	private JdbcTemplate jdbcTemplate;
	
	private LobHandler  lobHandler = new DefaultLobHandler();

	@Autowired
	public Dao(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public <T> List<T> querySingleColumnForList(String sql, Class<T> elementType,Object... args){
		long startTime = 0L;
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
			startTime = System.nanoTime();
		}
		List<T> list= this.jdbcTemplate.queryForList(sql,elementType,args);
		if(log.isDebugEnabled()){
			String f= _StopWatch.spendFormat(System.nanoTime() - startTime);
			//list始终不为空，jdbcTemplate有做result判断
			log.debug("\nTotal: {} ,Spend: {}",list.size(),f);
		}
		return list;
	}
	
	
	
	
	public int update(String sql,Object... args){
		long startTime = System.nanoTime();
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		int i;
		try {
			i= jdbcTemplate.update(sql, args);
		}catch(DuplicateKeyException ex) {
			log.error("主键或唯一索引重复:{}",ex.getMessage());
			throw ex;
		}
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nResult: {} rows affected, Spend: {}",i,f);
		}
		return i;
	}
	
	/**
	 * 返回last_insert_id
	 */
	public Number updateReturnGeneratedKeys(final String sql,final Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(
                    Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                new ArgumentPreparedStatementSetter(args).setValues(ps);
                return ps;
            }  
        }, keyHolder);
		return keyHolder.getKey();
	}
	public Map<String,Object> queryForMap(String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		long startTime = System.nanoTime();
		Map<String,Object> t=null;
		try{
			t= jdbcTemplate.queryForObject(sql,getColumnMapRowMapper(), args);
		}catch(EmptyResultDataAccessException ignored){}
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nResult: {} ,Spend: {}",t==null?"null":"object",f);
		}
		return t;
	}
	public Map<String,Object> queryForMap(boolean isToCamelCase,String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		long startTime = System.nanoTime();
		Map<String,Object> t=null;
		try{
			t= jdbcTemplate.queryForObject(sql,getColumnMapRowMapper(isToCamelCase), args);
		}catch(EmptyResultDataAccessException ignored){}
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nResult: {} ,Spend: {}",t==null?"null":"object",f);
		}
		return t;
	}
	//数据库字段转驼峰
	boolean mapUnderscoreToCamelCase=true;
	
	protected RowMapper<Map<String, Object>> getColumnMapRowMapper() {
		return mapUnderscoreToCamelCase?new ColumnMapCamelCaseRowMapper():new ColumnMapRowMapper();
	}
	protected RowMapper<Map<String, Object>> getColumnMapRowMapper(boolean isToCamelCase) {
		return isToCamelCase?new ColumnMapCamelCaseRowMapper():new ColumnMapRowMapper();
	}
	public <T> T querySingleColumn(String sql,Class<T> requiredType,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		long startTime = System.nanoTime();
		T t=null;
		try{
			t= jdbcTemplate.queryForObject(sql, requiredType, args);
		}catch(EmptyResultDataAccessException ignored){}
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nResult: {} ,Spend: {}",t==null?"null":t.toString(),f);
		}
		return t;
	}
	public <T> T queryForBean(String sql,Class<T> elementType,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		long startTime = System.nanoTime();
		T t=null;
		try{
			t= jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<T>(elementType), args);
		}catch(EmptyResultDataAccessException ignored){}
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nResult: {} ,Spend: {}",t==null?"null":"object",f);
		}
		return t;
		
	}
	
	public List<Map<String,Object>> queryForList(String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		long startTime = System.nanoTime();
		List<Map<String,Object>> list= jdbcTemplate.query(sql,getColumnMapRowMapper(),args);
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nTotal: {} ,Spend: {}",list.size(),f);
		}
		return list;
	}
	public List<Map<String,Object>> queryForList(boolean isToCamelCase,String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		long startTime = System.nanoTime();
		List<Map<String,Object>> list=  jdbcTemplate.query(sql,getColumnMapRowMapper(isToCamelCase),args);
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nTotal: {} ,Spend: {}",list.size(),f);
		}
		return list;
	}
	
	public <T> List<T> queryForBeanList(String sql, Class<T> elementType, Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		long startTime = System.nanoTime();
		List<T> list= jdbcTemplate.query(sql, new BeanPropertyRowMapper<T>(elementType), args);
		if(log.isDebugEnabled()){
			String f=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nTotal: {} ,Spend: {}",list.size(),f);
		}
		return list;
	}
	
	/**
	 * 批量更新
	 */
	public int[] batchUpdate(String sql,List<Object[]> batchArgs){
		long startTime = System.nanoTime();
		if(log.isDebugEnabled()){
			log.debug("Batch Executing prepared SQL:\n{}",sql);
			for(int i=0;i<batchArgs.size();i++) {
				Object[] arg=batchArgs.get(i);
				log.debug("BatchArguments[{}]:{}",i,Arrays.toString(arg));
			}
		}
		int[] ints= jdbcTemplate.batchUpdate(sql, batchArgs);
		if(log.isDebugEnabled()){
			String spendTime=_StopWatch.spendFormat(System.nanoTime() - startTime);
			log.debug("\nResult: {} ,Spend: {}",Arrays.toString(ints),spendTime);
		}
		return ints;
	}
	
	
	public void updateBlobAsBinaryStream(String sql,int paramIndex,int contentLength,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		jdbcTemplate.execute(sql,
			new AbstractLobCreatingPreparedStatementCallback(lobHandler){
				public void setValues(PreparedStatement ps,LobCreator lobCreator) throws SQLException,DataAccessException{
					int argLength=args.length;
					int forLength=argLength+1;
					for (int i = 1; i < forLength; i++) {
						Object arg=args[i-1];
						if(paramIndex==i){
							lobCreator.setBlobAsBinaryStream(ps, paramIndex, (InputStream)arg, contentLength);
						}else{
							ps.setObject(i, arg);
						}
					}
				}
			}
		);
	}

	public void updateBlobAsBytes(String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		jdbcTemplate.execute(sql,
			new AbstractLobCreatingPreparedStatementCallback(lobHandler){
				public void setValues(PreparedStatement preparedStatement,LobCreator lobCreator) throws SQLException,DataAccessException{
					int argLength=args.length;
					int forLength=argLength+1;
					for (int i = 1; i < forLength; i++){
						Object arg=args[i-1];
						if(arg!=null&&arg.getClass()==byte[].class){
							lobCreator.setBlobAsBytes(preparedStatement, i, (byte[])arg);
						}else{
							preparedStatement.setObject(i, arg);
						}
					}
				}
			}
		);
	}
	public void getBlobAsBinaryStream(OutputStream outputStream,String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		jdbcTemplate.query(sql,
		 		 new AbstractLobStreamingResultSetExtractor(){
		 			 public void streamData(ResultSet rs) throws SQLException, IOException {
		 				 FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 1), outputStream);
		              }
		          }
				, args);
	}
	public byte[] getBlobAsBytes(String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		return (byte[])jdbcTemplate.query(sql, args,
		 		 new ResultSetExtractor<byte[]>(){
		 			 public byte[] extractData(ResultSet rs) throws SQLException, DataAccessException {
		 				if (!rs.next()) {
		 					throw new EmptyResultDataAccessException(
		 							"LobStreamingResultSetExtractor did not find row in database", 1);
		 				}
		 				return lobHandler.getBlobAsBytes(rs, 1);
		 				 //FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 1), outputStream);
		              }
		          }
		  );
	}
	public Blob getBlob(String sql,Object... args){
		if(log.isDebugEnabled()){
			log.debug("Executing prepared SQL: \n{}\nParameters: {}",sql,Arrays.toString(args));
		}
		return (Blob)jdbcTemplate.query(sql, args,
		 		 new ResultSetExtractor<Blob>(){
		 			 public Blob extractData(ResultSet rs) throws SQLException, DataAccessException {
		 				if (!rs.next()) {
		 					throw new EmptyResultDataAccessException(
		 							"LobStreamingResultSetExtractor did not find row in database", 1);
		 				}
		 				return rs.getBlob(1);
		 				 //FileCopyUtils.copy(lobHandler.getBlobAsBinaryStream(rs, 1), outputStream);
		              }
		          }
		  );
	}
}
